go

exec dbo.p_drop_proc_if_exists 'meta_get_current_user';

go

--Function of getting current user
create procedure meta_get_current_user(
    @a_current_user_name varchar(30) output)
with execute as caller
as
begin
    set nocount on;
    select @a_current_user_name = original_login();
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_user_exists';

go

--Function of checking user existence
create procedure p_check_user_exists(
    --User name
    @a_user_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
    if not exists(select 1 from meta_users
        where user_name = lower(@a_user_name))
    begin
         raiserror('A user with the specified name was not found (user_name = %s) (#error_code = 20036)',
            16, 1, @a_user_name);
         set @a_result = 0;
         return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_user';

go

--Function of adding new user
create procedure meta_add_user(
    --User name
    @a_user_name varchar(max),
    --User password
    @a_user_password varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Row count
    @a_row_count numeric;
    
    set @a_user_name = lower(@a_user_name);
    
    --Searching for a user with the same name
    select @a_row_count = count(*)
    from meta_users
    where user_name = @a_user_name;
    
    if @a_row_count > 0
    begin
        raiserror('A user with the specified name was already added (user_name = %s) (#error_code = 20041)',
        16, 1, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    
    --If there is no user with the specified name
    if not exists(select 1 from sys.server_principals
		where lower(name) = @a_user_name)
	begin
		--If the user password was specified
        if @a_user_password is not null 
			and len(rtrim(ltrim((@a_user_password)))) > 0
			--Creating a new login (this operation cannot be inside a transaction)
			exec ('create login ' + @a_user_name
			+ ' with password = ''' + @a_user_password
			+ ''' , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' );
		--If the user password was not specified
		else
			--Creating a new login with the default password
			exec ('create login ' + @a_user_name
			+ ' with password = ''P@ssw0rd'' , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' );
	end;
	--If there is a user with the specified name and the new password was specified
	else if @a_user_password is not null 
			and len(rtrim(ltrim((@a_user_password)))) > 0
		--Altering the user password
		exec ('alter login ' + @a_user_name
		+ ' with password = ''' + @a_user_password + '''');

    --Creating a new user (this operation cannot be inside a transaction)
    exec ('create user ' + @a_user_name
    + ' for login ' + @a_user_name
    + ' with DEFAULT_SCHEMA = dbo');
    
    --If there is no db user with the specified name
    if not exists(select * from sysusers where name=@a_user_name)
		exec ('create user ' + @a_user_name
		+ ' for login ' + @a_user_name
		+ ' with DEFAULT_SCHEMA = dbo');
	--If there is a db user with the specified name
	else
		exec ('alter user ' + @a_user_name 
		+ ' with login = ' + @a_user_name);
    
    begin tran;

    --Updating the metadata
    insert into meta_users(user_name)
    values(@a_user_name);
    
    --Granting default priviliges
    
    --Read metadata version
    exec ('grant select on meta_version to ' + @a_user_name);
    
    --Read and write dbms features
    exec ('grant select on meta_dbms_features to ' + @a_user_name);
    exec ('grant update on meta_dbms_features to ' + @a_user_name);
    
    --Read schema metadata
    exec ('grant select on meta_tables to ' + @a_user_name);
    exec ('grant select on meta_string_columns to ' + @a_user_name);
    exec ('grant select on meta_ref_columns to ' + @a_user_name);
    exec ('grant select on meta_ref_targets to ' + @a_user_name);
    exec ('grant select on meta_enum_columns to ' + @a_user_name);
    exec ('grant select on meta_enum_members to ' + @a_user_name);
    exec ('grant select on meta_big_int_columns to ' + @a_user_name);
    exec ('grant select on meta_binary_columns to ' + @a_user_name);
    exec ('grant select on meta_bool_columns to ' + @a_user_name);
    exec ('grant select on meta_datetime_columns to ' + @a_user_name);
    exec ('grant select on meta_decimal_columns to ' + @a_user_name);
    exec ('grant select on meta_guid_columns to ' + @a_user_name);
    exec ('grant select on meta_int_columns to ' + @a_user_name);
    exec ('grant select on meta_small_int_columns to ' + @a_user_name);
    exec ('grant select on meta_text_columns to ' + @a_user_name);
    exec ('grant select on meta_table_columns to ' + @a_user_name);
    
    --Reading indexes
    exec ('grant select on meta_indexes to ' + @a_user_name);
    
    --Reading options
    exec ('grant select on meta_options to ' + @a_user_name);
    
    --Executing common functions
    exec ('grant execute on meta_get_dbms_name to ' + @a_user_name);
    exec ('grant execute on meta_get_dbms_version to ' + @a_user_name);
    exec ('grant execute on meta_get_current_db_name to ' + @a_user_name);
    exec ('grant execute on meta_get_current_user to ' + @a_user_name);
    exec ('grant execute on meta_set_curr_user_password to ' + @a_user_name);
    
    --Reading user names and display names
    exec ('grant select on meta_user_names to ' + @a_user_name);
    
    --Current user properties
    exec ('grant select on meta_current_user to ' + @a_user_name); 
    exec ('grant update(display_name) on meta_current_user to ' + @a_user_name); 
    
    --Read unit list
    exec ('grant select on meta_units to ' + @a_user_name);
    exec ('grant select on meta_unit_parents to ' + @a_user_name);
    exec ('grant select on meta_user_units to ' + @a_user_name);

    --Read user's acl unit-level access rights
    exec ('grant select on meta_user_acl_unit_access to ' + @a_user_name);
    --Read user's acl full access rights
    exec ('grant select on meta_user_acl_full_access to ' + @a_user_name);
    --Read user's acl own rows access rights
    exec ('grant select on meta_user_acl_own_rows to ' + @a_user_name);
    
    --Soft deleting table rows
    exec ('grant execute on meta_soft_delete_table_row to ' + @a_user_name);
    
    --Calling main security function (security check is implemented inside this function)
    exec ('grant execute on meta_set_user_change_security to ' + @a_user_name);
    
    --Granting login permission
    exec dbo.meta_set_user_login @a_user_name, 1;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_login';

go

--Function of allowing user login
create procedure meta_set_user_login(
    --User name
    @a_user_name varchar(max),
    --Can login
    @a_can_login numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Old CanLogin
    @a_old_can_login numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --If it is current user
    if lower(@a_user_name) = lower(original_login())
    begin
        raiserror('Changing current user login permission is forbidden (user_name = %s) (#error_code = 20042)',
            16, 1, @a_user_name);
        return;
    end;

    --Reading old CanLogin
    select @a_old_can_login = can_login
    from meta_users
    where user_name = lower(@a_user_name);
    
    --if there is no need for change
    if @a_can_login = @a_old_can_login
        return;
        
    set xact_abort on;
    begin tran;
    
    --Changing the metadata
    update meta_users
    set can_login = @a_can_login
    where user_name = lower(@a_user_name);
    
    --Granting or revoking the privilege
    if @a_can_login = 1
        exec ('grant connect to ' + @a_user_name);
    else
        exec ('revoke connect from ' + @a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_change_schema';

go

--Function of setting user CanChangeSchema
create procedure meta_set_user_change_schema(
    --User name
    @a_user_name varchar(max),
    --Can change schema
    @a_can_change_schema numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Current user can change security
    @a_user_can_change_security numeric(1),
    --Old CanChangeSchema
    @a_old_can_change_schema numeric(1),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --Reading old CanChangeSchema
    select @a_old_can_change_schema = can_change_schema
    from meta_users
    where user_name = lower(@a_user_name);
    
    --If there is no need for change
    if @a_can_change_schema = @a_old_can_change_schema
        return;
        
    set xact_abort on;
    begin tran;
    
    if @a_can_change_schema = 1
        exec dbo.p_enable_schema_change @a_user_name;
    else
        exec dbo.p_disable_schema_change @a_user_name;
    
    --Changing the metadata
    update meta_users
    set can_change_schema = @a_can_change_schema
    where user_name = lower(@a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_enable_schema_change';

go

--Function of enabling schema change
create procedure p_enable_schema_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Update metadata version
    exec ('grant update on meta_version to ' + @a_user_name);
    
    --Granting update on meta tables
    exec ('grant update(display_name, description) on meta_tables to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_string_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_ref_columns to ' + @a_user_name);
    exec ('grant update(cascade_delete) on meta_ref_targets to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_enum_columns to ' + @a_user_name);
    exec ('grant update(display_name) on meta_enum_members to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_big_int_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_binary_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_bool_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_datetime_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_decimal_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_guid_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_int_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_small_int_columns to ' + @a_user_name);
    exec ('grant update(display_name, description, order_index) on meta_text_columns to ' + @a_user_name);
    
    --Grant execute on schema changing functions
    exec ('grant execute on meta_add_table to ' + @a_user_name);
    exec ('grant execute on meta_delete_table to ' + @a_user_name);
    exec ('grant execute on meta_rename_table to ' + @a_user_name);
    exec ('grant execute on meta_add_string_column to ' + @a_user_name);
    exec ('grant execute on meta_delete_column to ' + @a_user_name);
    exec ('grant execute on meta_set_string_default to ' + @a_user_name);
    exec ('grant execute on meta_set_column_nullability to ' + @a_user_name);
    exec ('grant execute on meta_set_string_max_length to ' + @a_user_name);
    exec ('grant execute on meta_rename_column to ' + @a_user_name);
    exec ('grant execute on meta_add_ref_column to ' + @a_user_name);
    exec ('grant execute on meta_set_ref_default to ' + @a_user_name);
    exec ('grant execute on meta_add_ref_target to ' + @a_user_name);
    exec ('grant execute on meta_delete_ref_target to ' + @a_user_name);
    exec ('grant execute on meta_set_ref_checking to ' + @a_user_name);    
    exec ('grant execute on meta_add_enum_column to ' + @a_user_name);
    exec ('grant execute on meta_add_enum_member to ' + @a_user_name);
    exec ('grant execute on meta_delete_enum_member to ' + @a_user_name);
    exec ('grant execute on meta_set_enum_member_value to ' + @a_user_name);
    exec ('grant execute on meta_set_enum_default to ' + @a_user_name);
    exec ('grant execute on meta_set_enum_value_checking to ' + @a_user_name);
    exec ('grant execute on meta_add_decimal_column to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_minimum to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_precision to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_scale to ' + @a_user_name);
    exec ('grant execute on meta_set_decimal_default to ' + @a_user_name);
    exec ('grant execute on meta_add_datetime_column to ' + @a_user_name);
    exec ('grant execute on meta_set_datetime_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_datetime_minimum to ' + @a_user_name);
    exec ('grant execute on meta_set_datetime_default to ' + @a_user_name);
    exec ('grant execute on meta_add_bool_column to ' + @a_user_name);
    exec ('grant execute on meta_set_bool_default to ' + @a_user_name);
    exec ('grant execute on meta_add_binary_column to ' + @a_user_name);
    exec ('grant execute on meta_add_int_column to ' + @a_user_name);
    exec ('grant execute on meta_set_int_default to ' + @a_user_name);
    exec ('grant execute on meta_set_int_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_int_minimum to ' + @a_user_name);
    exec ('grant execute on meta_add_big_int_column to ' + @a_user_name);
    exec ('grant execute on meta_set_big_int_default to ' + @a_user_name);
    exec ('grant execute on meta_set_big_int_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_big_int_minimum to ' + @a_user_name);
    exec ('grant execute on meta_add_small_int_column to ' + @a_user_name);
    exec ('grant execute on meta_set_small_int_default to ' + @a_user_name);
    exec ('grant execute on meta_set_small_int_maximum to ' + @a_user_name);
    exec ('grant execute on meta_set_small_int_minimum to ' + @a_user_name);
    exec ('grant execute on meta_add_guid_column to ' + @a_user_name);
    exec ('grant execute on meta_set_guid_default to ' + @a_user_name);
    exec ('grant execute on meta_add_text_column to ' + @a_user_name);
    exec ('grant execute on meta_add_index to ' + @a_user_name);
    exec ('grant execute on meta_delete_index to ' + @a_user_name);
    
    --Restoring table rows
    exec ('grant execute on meta_restore_table_row to ' + @a_user_name);    
    --Deleting table rows
    exec ('grant execute on meta_delete_table_row to ' + @a_user_name);
    
    --Enabling/disabling recycle bin
    exec ('grant execute on meta_set_recycle_bin to ' + @a_user_name);
    
    --Granting all rights to all user tables
    declare @a_table_name varchar(26),
    @a_table_cursor cursor;
    
    set @a_table_cursor = cursor fast_forward read_only for
    select table_name from meta_tables;
    
    open @a_table_cursor;
    fetch next from @a_table_cursor into @a_table_name;
    
    while @@fetch_status = 0
    begin
        exec ('grant select, insert, update, delete on tbl_'
         + @a_table_name + ' to ' + @a_user_name);
        fetch next from @a_table_cursor into @a_table_name;
    end;
    
    close @a_table_cursor;
    deallocate @a_table_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_disable_schema_change';

go

--Function of disabling schema change
create procedure p_disable_schema_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Revoking rights

    --Update metadata version
    exec ('revoke update on meta_version from ' + @a_user_name);
    
    --Revoking update on meta tables
    exec ('revoke update on meta_tables from ' + @a_user_name);
    exec ('revoke update on meta_string_columns from ' + @a_user_name);
    exec ('revoke update on meta_ref_columns from ' + @a_user_name);
    exec ('revoke update on meta_ref_targets from ' + @a_user_name);
    exec ('revoke update on meta_enum_columns from ' + @a_user_name);
    exec ('revoke update on meta_enum_members from ' + @a_user_name);
    exec ('revoke update on meta_big_int_columns from ' + @a_user_name);
    exec ('revoke update on meta_binary_columns from ' + @a_user_name);
    exec ('revoke update on meta_bool_columns from ' + @a_user_name);
    exec ('revoke update on meta_datetime_columns from ' + @a_user_name);
    exec ('revoke update on meta_decimal_columns from ' + @a_user_name);
    exec ('revoke update on meta_guid_columns from ' + @a_user_name);
    exec ('revoke update on meta_int_columns from ' + @a_user_name);
    exec ('revoke update on meta_small_int_columns from ' + @a_user_name);
    exec ('revoke update on meta_text_columns from ' + @a_user_name);
    
    --Grant execute on schema changing functions
    exec ('revoke execute on meta_add_table from ' + @a_user_name);
    exec ('revoke execute on meta_delete_table from ' + @a_user_name);
    exec ('revoke execute on meta_rename_table from ' + @a_user_name);
    exec ('revoke execute on meta_add_string_column from ' + @a_user_name);
    exec ('revoke execute on meta_delete_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_string_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_column_nullability from ' + @a_user_name);
    exec ('revoke execute on meta_set_string_max_length from ' + @a_user_name);
    exec ('revoke execute on meta_rename_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_ref_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_ref_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_ref_target from ' + @a_user_name);
    exec ('revoke execute on meta_delete_ref_target from ' + @a_user_name);
    exec ('revoke execute on meta_set_ref_checking from ' + @a_user_name);
    exec ('revoke execute on meta_add_enum_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_enum_member from ' + @a_user_name);
    exec ('revoke execute on meta_delete_enum_member from ' + @a_user_name);
    exec ('revoke execute on meta_set_enum_member_value from ' + @a_user_name);
    exec ('revoke execute on meta_set_enum_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_enum_value_checking from ' + @a_user_name);
    exec ('revoke execute on meta_add_decimal_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_precision from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_scale from ' + @a_user_name);
    exec ('revoke execute on meta_set_decimal_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_datetime_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_datetime_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_datetime_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_set_datetime_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_bool_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_bool_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_binary_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_int_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_int_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_int_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_int_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_add_big_int_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_big_int_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_big_int_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_big_int_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_add_small_int_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_small_int_default from ' + @a_user_name);
    exec ('revoke execute on meta_set_small_int_maximum from ' + @a_user_name);
    exec ('revoke execute on meta_set_small_int_minimum from ' + @a_user_name);
    exec ('revoke execute on meta_add_guid_column from ' + @a_user_name);
    exec ('revoke execute on meta_set_guid_default from ' + @a_user_name);
    exec ('revoke execute on meta_add_text_column from ' + @a_user_name);
    exec ('revoke execute on meta_add_index from ' + @a_user_name);
    exec ('revoke execute on meta_delete_index from ' + @a_user_name);
    
    --Restoring table rows
    exec ('revoke execute on meta_restore_table_row from ' + @a_user_name);    
    --Deleting table rows
    exec ('revoke execute on meta_delete_table_row from ' + @a_user_name);
    
    --Enabling/disabling recycle bin
    exec ('revoke execute on meta_set_recycle_bin from ' + @a_user_name);
    
    --Revoking all rights from all user tables
    declare @a_table_name varchar(26),
    @a_table_cursor cursor;
    
    set @a_table_cursor = cursor fast_forward read_only for
    select table_name from meta_tables;
    
    open @a_table_cursor;
    fetch next from @a_table_cursor into @a_table_name;
    
    while @@fetch_status = 0
    begin
        exec ('revoke select, insert, update, delete on tbl_'
         + @a_table_name + ' from ' + @a_user_name);
        fetch next from @a_table_cursor into @a_table_name;
    end;
    
    close @a_table_cursor;
    deallocate @a_table_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_change_security';

go

--Function of setting user's ability to change security
create procedure meta_set_user_change_security(
    --User name
    @a_user_name varchar(max),
    --Can change security
    @a_can_change_security numeric(1))
with execute as owner
as
begin
    set nocount on;
    declare
    --Old CanChangeSecurity
    @a_old_can_change_security numeric(1),
    --Current user CanChangeSecurity
    @a_curr_user_change_security numeric(1),
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --If it is current user
    if lower(@a_user_name) = lower(original_login())
    begin
        raiserror('Changing current user security change permission is forbidden (user_name = %s) (#error_code = 20043)',
        16, 1, @a_user_name);
        return;
    end;
    
    --Searching for current user
    select @a_row_count = count(*)
    from meta_users
    where user_name = lower(original_login());
    
    --If current user was found
    if @a_row_count <> 0
    begin
        --Reading current user ability to change security
        select @a_curr_user_change_security = can_change_security
        from meta_users
        where user_name = lower(original_login());
        
        if @a_curr_user_change_security = 0
        begin
            raiserror('Insufficient privileges to change another user security permissions (user_name = %s) (#error_code = 20045)',
                16, 1, @a_user_name);
            return;
        end;
    end;

    --Reading old CanChangeSecurity
    select @a_old_can_change_security = can_change_security
    from meta_users
    where user_name = lower(@a_user_name);
    
    --If there is no need for change
    if @a_can_change_security = @a_old_can_change_security
        return;
        
    set xact_abort on;
    begin tran;
    
    if @a_can_change_security = 1
        exec dbo.p_enable_security_change @a_user_name;
    else
        exec dbo.p_disable_security_change @a_user_name;
    
     --Changing the metadata
    update meta_users
    set can_change_security = @a_can_change_security
    where user_name = lower(@a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_enable_security_change';

go

--Function of enabling security change
create procedure p_enable_security_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Granting read on security metadata
    exec ('grant select on meta_users to ' + @a_user_name);
    exec ('grant select on meta_user_roles to ' + @a_user_name);
    exec ('grant select on meta_role_roles to ' + @a_user_name);
    exec ('grant select on meta_roles to ' + @a_user_name);
    exec ('grant select on meta_principals to ' + @a_user_name);
    exec ('grant select on meta_table_acl to ' + @a_user_name);
    exec ('grant select on meta_table_acl_unit_access to ' + @a_user_name);
    exec ('grant select on meta_table_acl_full_access to ' + @a_user_name);
    exec ('grant select on meta_table_acl_own_rows to ' + @a_user_name);
    
    --Granting update on security metadata
    exec ('grant update(display_name) on meta_users to ' + @a_user_name);
    exec ('grant update(unit_name, display_name) on meta_units to ' + @a_user_name);
    exec ('grant update(display_name) on meta_roles to ' + @a_user_name);
    
    --Granting execute on security admin functions
    exec ('grant execute on meta_add_unit to ' + @a_user_name);
    exec ('grant execute on meta_delete_unit to ' + @a_user_name);
    exec ('grant execute on meta_add_unit_parent to ' + @a_user_name);
    exec ('grant execute on meta_delete_unit_parent to ' + @a_user_name);
    exec ('grant execute on meta_add_role to ' + @a_user_name);
    exec ('grant execute on meta_delete_role to ' + @a_user_name);
    exec ('grant execute on meta_add_user to ' + @a_user_name);
    exec ('grant execute on meta_delete_user to ' + @a_user_name);
    exec ('grant execute on meta_set_user_login to ' + @a_user_name);
    exec ('grant execute on meta_set_user_change_schema to ' + @a_user_name);
    exec ('grant execute on meta_add_user_unit to ' + @a_user_name);
    exec ('grant execute on meta_delete_user_unit to ' + @a_user_name);
    exec ('grant execute on meta_add_user_in_role to ' + @a_user_name);
    exec ('grant execute on meta_delete_user_from_role to ' + @a_user_name);
    exec ('grant execute on meta_add_role_in_role to ' + @a_user_name);
    exec ('grant execute on meta_delete_role_from_role to ' + @a_user_name);
    exec ('grant execute on meta_add_table_rights to ' + @a_user_name);
    exec ('grant execute on meta_delete_table_rights to ' + @a_user_name);
    exec ('grant execute on meta_set_user_password to ' + @a_user_name);
    exec ('grant execute on meta_set_unit_security to ' + @a_user_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_disable_security_change';

go

--Function of disabling user ability to change security
create procedure p_disable_security_change(
    --User name
    @a_user_name varchar(26))
as
begin
    --Revoking read on security metadata
    exec ('revoke select on meta_users from ' + @a_user_name);
    exec ('revoke select on meta_user_roles from ' + @a_user_name);
    exec ('revoke select on meta_role_roles from ' + @a_user_name);
    exec ('revoke select on meta_roles from ' + @a_user_name);
    exec ('revoke select on meta_principals from ' + @a_user_name);
    exec ('revoke select on meta_table_acl from ' + @a_user_name);
    exec ('revoke select on meta_table_acl_unit_access from ' + @a_user_name);
    exec ('revoke select on meta_table_acl_full_access from ' + @a_user_name);
    exec ('revoke select on meta_table_acl_own_rows from ' + @a_user_name);
    
    --Revoking update on security metadata
    exec ('revoke update on meta_users from ' + @a_user_name);
    exec ('revoke update on meta_units from ' + @a_user_name);
    exec ('revoke update on meta_roles from ' + @a_user_name);
    
    --Revoking execute on security admin functions
    exec ('revoke execute on meta_add_unit from ' + @a_user_name);
    exec ('revoke execute on meta_delete_unit from ' + @a_user_name);
    exec ('revoke execute on meta_add_unit_parent from ' + @a_user_name);
    exec ('revoke execute on meta_delete_unit_parent from ' + @a_user_name);
    exec ('revoke execute on meta_add_role from ' + @a_user_name);
    exec ('revoke execute on meta_delete_role from ' + @a_user_name);
    exec ('revoke execute on meta_add_user from ' + @a_user_name);
    exec ('revoke execute on meta_delete_user from ' + @a_user_name);
    exec ('revoke execute on meta_set_user_login from ' + @a_user_name);
    exec ('revoke execute on meta_set_user_change_schema from ' + @a_user_name);
    exec ('revoke execute on meta_add_user_unit from ' + @a_user_name);
    exec ('revoke execute on meta_delete_user_unit from ' + @a_user_name);
    exec ('revoke execute on meta_add_user_in_role from ' + @a_user_name);
    exec ('revoke execute on meta_delete_user_from_role from ' + @a_user_name);
    exec ('revoke execute on meta_add_role_in_role from ' + @a_user_name);
    exec ('revoke execute on meta_delete_role_from_role from ' + @a_user_name);
    exec ('revoke execute on meta_add_table_rights from ' + @a_user_name);
    exec ('revoke execute on meta_delete_table_rights from ' + @a_user_name);
    exec ('revoke execute on meta_set_user_password from ' + @a_user_name);
    exec ('revoke execute on meta_set_unit_security from ' + @a_user_name);    
    
    --!!! No need to revoke execute from meta_set_user_change_security
    -- Otherwise there might be a deadlock
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_user';

go

--Function of deleting a user
create procedure meta_delete_user(
    --User name
    @a_user_name varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1);   
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    --If it is current user
    if lower(@a_user_name) = lower(original_login())
    begin
        raiserror('Deleting current user is forbidden (user_name = %s) (#error_code = 20046)',
        16, 1, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Deleting the user
    exec ('drop user ' + @a_user_name);    
   
    --Updating the metadata
    delete from meta_users
    where user_name = lower(@a_user_name);
    
    --Deleting user acl
    delete from meta_table_acl
    where principal_name = lower(@a_user_name);
    
    commit;
end;

go

exec dbo.p_drop_func_if_exists 'p_get_user_principals';

go

--Function of getting user principals
create function p_get_user_principals(
    --User name
    @a_user_name varchar(26))
    returns table
as
return
(
    with cte_user_roles(role_name) as
    (
        select container_role_name
        from meta_user_roles
        where user_name = lower(@a_user_name)
        union all
        select e.container_role_name
        from meta_role_roles as e 
        inner join cte_user_roles as d on d.role_name = e.role_name
    )
    select distinct role_name as principal_name
    from cte_user_roles
    union
    select @a_user_name
);

go

exec dbo.p_drop_proc_if_exists 'meta_add_user_unit';

go

--Function of adding user's unit
create procedure meta_add_user_unit(
    --User name
    @a_user_name varchar(max),
    --Unit id
    @a_unit_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Unit id str
    @a_unit_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;

    --Searching for the same user unit
    if exists(select 1 from meta_user_units
        where user_name = lower(@a_user_name)
        and unit_id = @a_unit_id)
    begin
        set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
        raiserror('Specified user unit was already added (unit_id = %s, user_name = %s) (#error_code = 20052)',
        16, 1, @a_unit_id_str, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_user_units (user_name, unit_id)
    values (@a_user_name, @a_unit_id);
    
    --Refreshing the user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_user_unit';

go

--Function of deleting user's unit
create procedure meta_delete_user_unit(
    --User name
    @a_user_name varchar(max),
    --Unit id
    @a_unit_id binary(16))
with execute as owner
as
begin
    set nocount on;
    declare
    --Unit id str
    @a_unit_id_str varchar(32),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;
    
    exec dbo.p_check_unit_exists @a_unit_id, @a_result output;
    
    if @a_result = 0 return;

    --Searching for the user unit
    if not exists(select 1 from meta_user_units
        where user_name = lower(@a_user_name)
        and unit_id = @a_unit_id)
    begin
        set @a_unit_id_str = dbo.p_bin_to_str(@a_unit_id);
        raiserror('Specified user unit was not found (unit_id = %s, user_name = %s) (#error_code = 20053)',
        16, 1, @a_unit_id_str, @a_user_name);
        return;
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    delete from meta_user_units
    where user_name = lower(@a_user_name)
    and unit_id = @a_unit_id;
    
    --Refreshing the user acl
    exec dbo.p_refresh_user_acl @a_user_name;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_user_password';

go

--Function of changing user password
create procedure meta_set_user_password(
    --User name
    @a_user_name varchar(max),
    --New password
    @a_new_password varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Row count
    @a_row_count numeric,
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_user_exists @a_user_name, @a_result output;
    
    if @a_result = 0 return;

    set xact_abort on;
    begin tran;

    exec ('alter login ' + @a_user_name
    + ' with password = ''' + @a_new_password + '''');
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_curr_user_password';

go

--Function of changing current user password
create procedure meta_set_curr_user_password(
    --Old password
    @a_old_password varchar(max),
    --New password
    @a_new_password varchar(max))
with execute as self
as
begin
    set nocount on;
    declare
    --Result
    @a_result numeric(1),
    --Current user
    @a_curr_user varchar(255);
    
    set @a_curr_user = original_login();
    
    exec dbo.p_check_user_exists @a_curr_user, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    exec ('alter login ' + @a_curr_user
    + ' with password = ''' + @a_new_password
    + ''' old_password = ''' + @a_old_password + '''');
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_unit_security';

go

--Function of enabling/disabling table unit level security
create procedure meta_set_unit_security(
    --Table id
    @a_table_id binary(16),
    --Unit level security
    @a_unit_level_security numeric(1))
with execute as owner
as    
begin
    set nocount on;
    
    declare 
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);

    --Updating the metadata
    update meta_tables
    set unit_level_security = @a_unit_level_security
    where table_id = @a_table_id;

    --Recreating table view
    exec p_drop_view_if_exists @a_table_name;

    exec p_create_table_view @a_table_id;
    
    commit;     
end;